﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using ERPStudio;
using System.Data.SqlClient;

namespace ResUma
{
    public partial class PontajFrm : FrmRezumat
    {

        private Int16 AnulCurent = 0;
        private Int16 LunaCurenta = 0;
        private int defaultDepartament = 0;
        public PontajFrm(ModuleClass mInfo)
            : base(mInfo)
        {
            InitializeComponent();
            workConnString = "ESRPConnectionString";
        }


        private void FormatGridPontaj(SqlConnection con)
        {
            /////////////coloana nume prenume
            DataGridViewTextBoxColumn dc = new DataGridViewTextBoxColumn();
            dc.HeaderText = "Nume Persoana";
            dc.Name = "NumePersoanaColumn";
            dc.ValueType = typeof(String);
            dc.Frozen = true;
            dc.ReadOnly = true;
            dc.DefaultCellStyle.BackColor = Color.LightGray;
            dc.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            pontajDGV.Columns.Add(dc);

            //////////////////////coloana total ore
            dc = new DataGridViewTextBoxColumn();
            dc.HeaderText = "Total Ore";
            dc.Name = "TotalOreColumn";
            dc.ReadOnly = true;
            dc.Width = 40;
            dc.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            dc.DefaultCellStyle.Format = "D0";
            dc.DefaultCellStyle.BackColor = Color.LightGray;

            dc.ValueType = typeof(Decimal);
            dc.Frozen = true;
            pontajDGV.Columns.Add(dc);

            pontajDGV.m_VerticalHeaderColumns.Add(dc);

            SqlCommand cm = new SqlCommand("SELECT * FROM [TipPrezenta] WHERE [Selectat]=1", con);
            SqlDataReader reader = cm.ExecuteReader();
            while (reader.Read())
            {
                dc = new DataGridViewTextBoxColumn();
                dc.HeaderText = reader["Descriere"].ToString().ToLower();
                dc.Name = "Column" + reader["IdTipPrezenta"].ToString().Replace(' ', '_');
                dc.Width = 40;
                dc.ValueType = typeof(Int32);
                dc.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dc.DefaultCellStyle.Format = "D0";
                dc.Tag = new InfoPrezenta(reader["TimpBaza"].ToString(), 0, Decimal.Zero, Convert.ToInt32(reader["IdTipPrezenta"]), 0, false);
                pontajDGV.Columns.Add(dc);
                pontajDGV.m_VerticalHeaderColumns.Add(dc);
            }
            reader.Close();
        
        }

        private void FormatGridAdaosuri(SqlConnection con)
        {
            /////////////coloana nume prenume
            DataGridViewTextBoxColumn dc = new DataGridViewTextBoxColumn();
            dc.HeaderText = "Nume Persoana";
            dc.Name = "NumePersoanaColumn";
            dc.ValueType = typeof(String);
            dc.Frozen = true;
            dc.ReadOnly = true;
            dc.DefaultCellStyle.BackColor = Color.LightGray;
            dc.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            adaosuriDGV.Columns.Add(dc);

            //////////////////////coloana total ore
            dc = new DataGridViewTextBoxColumn();
            dc.HeaderText = "Total";
            dc.Name = "TotalColumn";
            dc.ReadOnly = true;
            dc.Width = 50;
            dc.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            dc.DefaultCellStyle.Format = "N0";
            dc.DefaultCellStyle.BackColor = Color.LightGray;

            dc.ValueType = typeof(Decimal);
            dc.Frozen = true;
            adaosuriDGV.Columns.Add(dc);

            pontajDGV.m_VerticalHeaderColumns.Add(dc);

            SqlCommand cm = new SqlCommand("SELECT * FROM [TipAdaos] WHERE [Selectat]=1", con);
            SqlDataReader reader = cm.ExecuteReader();
            while (reader.Read())
            {
                dc = new DataGridViewTextBoxColumn();
                dc.HeaderText = reader["Descriere"].ToString().ToLower();
                dc.Name = "Column" + reader["IdTipAdaos"].ToString().Replace(' ', '_');
                dc.Width = 50;
                dc.ValueType = typeof(Decimal);
                dc.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dc.DefaultCellStyle.Format = "N0";
                dc.Tag = new InfoPrezenta("", 0, Decimal.Zero, Convert.ToInt32(reader["IdTipAdaos"]), 0, false);
                adaosuriDGV.Columns.Add(dc);
                adaosuriDGV.m_VerticalHeaderColumns.Add(dc);
            }
            reader.Close();

        }


        private void PontajFrm_Load(object sender, EventArgs e)
        {
            SqlConnection con = DeschideConexiune(workConnString);
            this.departamenteTableAdapter.Connection = con;
            this.departamenteTableAdapter.Fill(eSRPDataSet.Departamente, ModuleInfo.CodUnitate);
            LoadSetup(con);
            FormatGridPontaj(con);
            FormatGridAdaosuri(con);

            //randuri
            departamentCB.SelectedValue = Convert.ToInt32(GetSetariLocale(con, "DIPT", "0", ModuleInfo.CodUnitate, ModuleInfo.UserID));
            InchideConexiune(con);
            
        }

        /// <summary>
        /// citeste setarile din ResUmaSetup
        /// </summary>
        /// <param name="sqlConn"></param>
        private void LoadSetup(SqlConnection sqlConn)
        {
            Object ret = GetSetupValue("ResUmaSetup", "AnulCurent", ModuleInfo.CodUnitate, sqlConn);
            /// nu a fost configurat modulul
            /// 
            while (ret == null)
            {
                MessageBox.Show("Modulul Resurse Umane nu a fost configurat. \nVa rugam sa-l configurati!",
                        "Eroare",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Hand);
                ResUmaSetupFrm frm = new ResUmaSetupFrm(ModuleInfo);
                frm.ShowDialog();
                ret = GetSetupValue("ResUmaSetup", "AnulCurent", ModuleInfo.CodUnitate, sqlConn);
            }
            AnulCurent = (Int16)ret;
            ret = GetSetupValue("ResUmaSetup", "LunaCurenta", ModuleInfo.CodUnitate, sqlConn);
            LunaCurenta = (Int16)ret;
            this.eSRPDataSet.AngajatiPontaj.LunaColumn.DefaultValue = LunaCurenta;
            this.eSRPDataSet.AngajatiPontaj.AnulColumn.DefaultValue = AnulCurent;

            SetInfoLunaAnul(LunaCurenta.ToString() + "," + AnulCurent.ToString());
            
            
        }

        private void departamentCB_SelectedIndexChanged(object sender, EventArgs e)
        {
            Salvare(sender, e);
            if (departamentCB.SelectedItem == null)
                return;
            ESRPDataSet.DepartamenteRow ddr = ((DataRowView)departamentCB.SelectedItem).Row as ESRPDataSet.DepartamenteRow;
            if (ddr.IDDepartament==defaultDepartament)
                return;
            Int32 anLuna = AnulCurent * 100 + LunaCurenta;
            SqlConnection con = DeschideConexiune(workConnString);
            SqlConnection con2 = DeschideConexiune(workConnString);
            SqlCommand cm = new SqlCommand("SELECT * FROM [resuma_PersonalAngajat] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate + " AND  [IDDepartament]=" +  ddr.IDDepartament.ToString() +
                " AND YEAR(DeLaData)*100+MONTH(DeLaData)<=" + anLuna.ToString() +
                "AND (PanaLaData IS NULL OR " + anLuna.ToString() + "<=YEAR(PanaLaData)*100+MONTH(PanaLaData)) " +
                "AND (DataPlecarii IS NULL OR " + anLuna.ToString() + "<=YEAR(DataPlecarii)*100+MONTH(DataPlecarii))", con);
            SqlDataReader reader = cm.ExecuteReader();
            pontajDGV.Rows.Clear();
            adaosuriDGV.Rows.Clear();
            Decimal total;
            int rAdd = 0;
            while (reader.Read())
            {
                DataGridViewRow drr = new DataGridViewRow(), drra = new DataGridViewRow();
                for (int i = 0; i < pontajDGV.Columns.Count; i++)
                    drr.Cells.Add(new DataGridViewTextBoxCell());
                for (int i = 0; i < adaosuriDGV.Columns.Count; i++)
                    drra.Cells.Add(new DataGridViewTextBoxCell());

                /// adaosuri-----------------------------------
                if (adaosuriDGV.Columns.Count != 0)
                {
                    rAdd = adaosuriDGV.Rows.Add(drra);
                    total = Decimal.Zero;
                    adaosuriDGV["NumePersoanaColumn", rAdd].Value = reader["NumePrenume"].ToString();
                    cm.CommandText = "SELECT AnsP.* FROM [AngajatiAdaosuri] AnsP JOIN [TipAdaos] Tp ON AnsP.IDTipAdaos=Tp.IDTipAdaos " +
                        " JOIN [AngajatiSalarizare] AnsS ON AnsS.IDAngajatSalarizare=AnsP.IDAngajatSalarizare " +
                        " WHERE AnsP.[IDpersoana]=" + reader["IdPersoana"].ToString() + " AND  AnsP.[IDAngajatSalarizare]=" + reader["IDAngajatSalarizare"].ToString();
                    cm.Connection = con2;
                    SqlDataReader reader2 = cm.ExecuteReader();
                    while (reader2.Read())
                    {
                        String numeColoana = "Column" + reader2["IdTipAdaos"].ToString().Replace(' ', '_');
                        if (adaosuriDGV.Columns.Contains(numeColoana))
                        {
                            adaosuriDGV[numeColoana, rAdd].Value = reader2["Suma"].ToString();
                            adaosuriDGV[numeColoana, rAdd].Tag = new InfoPrezenta("", Convert.ToInt32(reader2["IDPersoana"]),
                                Convert.ToDecimal(reader2["Suma"]), Convert.ToInt32(reader2["IdTipAdaos"]), Convert.ToInt32(reader["IDAngajatSalarizare"]), false);
                            total += Convert.ToDecimal(reader2["Suma"]);
                        }
                    }

                    ///completeaza celulele care nu au fost incarcate
                    ///
                    foreach (DataGridViewCell cl in adaosuriDGV.Rows[rAdd].Cells)
                    {
                        if ((cl.ColumnIndex > adaosuriDGV.Columns["TotalColumn"].Index) && (cl.Tag == null))
                        {
                            InfoPrezenta colp = adaosuriDGV.Columns[cl.ColumnIndex].Tag as InfoPrezenta;
                            if (colp != null)
                                cl.Tag = new InfoPrezenta("", Convert.ToInt32(reader["IDPersoana"]), Decimal.Zero, colp.IDTipPrezenta, Convert.ToInt32(reader["IDAngajatSalarizare"]), true);
                        }
                    }
                    reader2.Close();
                    adaosuriDGV["TotalColumn", rAdd].Value = total.ToString();
                }
                /// pontaje-----------------------------------------------
                if (pontajDGV.Columns.Count != 0)
                {
                    rAdd = pontajDGV.Rows.Add(drr);
                    total = Decimal.Zero;
                    pontajDGV["NumePersoanaColumn", rAdd].Value = reader["NumePrenume"].ToString();
                    cm.CommandText = "SELECT AnsP.*,Tp.TimpBaza,AnsS.OreNorma FROM [AngajatiPontaj] AnsP JOIN [TipPrezenta] Tp ON AnsP.IDTipPrezenta=Tp.IDTipPrezenta "+
                        " JOIN [AngajatiSalarizare] AnsS ON AnsS.IDAngajatSalarizare=AnsP.IDAngajatSalarizare " +
                        " WHERE AnsP.[IDpersoana]=" + reader["IdPersoana"].ToString() + " AND  AnsP.[IDAngajatSalarizare]=" + reader["IDAngajatSalarizare"].ToString();
                    cm.Connection = con2;
                    SqlDataReader reader2 = cm.ExecuteReader();
                    while (reader2.Read())
                    {
                        String numeColoana = "Column" + reader2["IdTipPrezenta"].ToString().Replace(' ', '_');
                        if (pontajDGV.Columns.Contains(numeColoana))
                        {
                            pontajDGV[numeColoana, rAdd].Value = reader2["Valoare"].ToString();
                            pontajDGV[numeColoana, rAdd].Tag = new InfoPrezenta(reader2["TimpBaza"].ToString(), Convert.ToInt32(reader2["IDPersoana"]),
                                Convert.ToDecimal(reader2["Valoare"]), Convert.ToInt32(reader2["IdTipPrezenta"]), Convert.ToInt32(reader["IDAngajatSalarizare"]),false);
                               
                            if(reader2["TimpBaza"].ToString() =="O")
                            {
                                total += Convert.ToDecimal(reader2["Valoare"]);
                            }
                            if (reader2["TimpBaza"].ToString() == "Z")
                            {
                                total += Convert.ToDecimal(reader2["Valoare"]) * Convert.ToDecimal(reader2["OreNorma"]);
                            }

                        }
                    }
                    
                    ///completeaza celulele care nu au fost incarcate
                    ///
                    foreach (DataGridViewCell cl in pontajDGV.Rows[rAdd].Cells)
                    {
                        if ((cl.ColumnIndex > pontajDGV.Columns["TotalOreColumn"].Index) && (cl.Tag==null))
                        { 
                            InfoPrezenta colp = pontajDGV.Columns[cl.ColumnIndex].Tag as InfoPrezenta;
                            if (colp != null)
                                cl.Tag = new InfoPrezenta(colp.TimpBaza, Convert.ToInt32(reader["IDPersoana"]), Decimal.Zero, colp.IDTipPrezenta, Convert.ToInt32(reader["IDAngajatSalarizare"]),true);
                        }
                    }
                    reader2.Close();
                    pontajDGV["TotalOreColumn", rAdd].Value = total.ToString();
                }
            }
            reader.Close();
            defaultDepartament = ddr.IDDepartament;
            InchideConexiune(con);
            
        }

        private void pontajDGV_CellValidated(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex <= pontajDGV.Columns["TotalOreColumn"].Index)
                return;
            Decimal total = Decimal.Zero;
            foreach(DataGridViewCell dc in pontajDGV.Rows[e.RowIndex].Cells)
            {
                if (dc.ColumnIndex > pontajDGV.Columns["TotalOreColumn"].Index)
                    if (dc.EditedFormattedValue.ToString() != String.Empty)
                        total += Convert.ToDecimal(dc.EditedFormattedValue);
            }
            pontajDGV["TotalOreColumn", e.RowIndex].Value = total.ToString();
        }

        private void PontajFrm_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (departamentCB.SelectedValue != null)
            {
                SqlConnection con = DeschideConexiune(workConnString);
                SetSetariLocale(con, "DIPT", departamentCB.SelectedValue.ToString(), ModuleInfo.CodUnitate, ModuleInfo.UserID);
                InchideConexiune(con);
            }
        }

        override public void Salvare(object sender, EventArgs args)
        {
            if (pontajDGV.Rows.Count == 0)
                return;
            SqlConnection con = DeschideConexiune(workConnString);
            SqlTransaction tr = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand(String.Empty, con, tr);
            int cate = 0;
            foreach(DataGridViewRow dr in pontajDGV.Rows)
                foreach (DataGridViewCell dc in dr.Cells)
                {
                    if (dc.ColumnIndex <= pontajDGV.Columns["TotalOreColumn"].Index)
                        continue;
                    InfoPrezenta infop = dc.Tag as InfoPrezenta;
                    Decimal valcur = Decimal.Zero;
                    if(dc.EditedFormattedValue.ToString()!=String.Empty)
                        valcur = Convert.ToDecimal(dc.EditedFormattedValue);
                    if (infop.ValoareInitiala != valcur)
                    {
                        if (infop.Insert)
                        {
                            cmd.CommandText = "INSERT INTO [AngajatiPontaj]([Valoare],[IdPersoana],[IdTipPrezenta],[IdAngajatSalarizare],[DataDefinire],[SalarUnitar],[Luna],[Anul]) " +
                                "VALUES (" + valcur.ToString() + "," + infop.IDPersoana.ToString() + "," + infop.IDTipPrezenta.ToString() + "," + infop.IDAngajatSalarizare.ToString() + ",GETDATE(),0," + LunaCurenta.ToString() + "," + AnulCurent.ToString() + ")";
                        }
                        else
                        {
                            if(valcur==Decimal.Zero)
                                cmd.CommandText = "DELETE FROM [AngajatiPontaj] WHERE [IdPersoana] =" + infop.IDPersoana.ToString() + " AND [IdTipPrezenta]=" + infop.IDTipPrezenta.ToString() + " AND [IdAngajatSalarizare]="+
                                 infop.IDAngajatSalarizare.ToString();
                            else
                                cmd.CommandText = "UPDATE [AngajatiPontaj] SET [Valoare] =" + valcur.ToString() + " WHERE [IdPersoana] =" + infop.IDPersoana.ToString() + " AND [IdTipPrezenta]=" + infop.IDTipPrezenta.ToString() + " AND [IdAngajatSalarizare]=" +
                                 infop.IDAngajatSalarizare.ToString(); ;

                        }
                        cate++;
                        cmd.ExecuteNonQuery();
                        infop.ValoareInitiala = valcur;
                    }
                }
            foreach (DataGridViewRow dr in adaosuriDGV.Rows)
                foreach (DataGridViewCell dc in dr.Cells)
                {
                    if (dc.ColumnIndex <= adaosuriDGV.Columns["TotalColumn"].Index)
                        continue;
                    InfoPrezenta infop = dc.Tag as InfoPrezenta;
                    Decimal valcur = Decimal.Zero;
                    if (dc.EditedFormattedValue.ToString() != String.Empty)
                        valcur = Convert.ToDecimal(dc.EditedFormattedValue);
                    if (infop.ValoareInitiala != valcur)
                    {
                        if (infop.Insert)
                        {
                            cmd.CommandText = "INSERT INTO [AngajatiAdaosuri]([Suma],[IdPersoana],[IdTipAdaos],[IdAngajatSalarizare],[Luna],[Anul]) " +
                                "VALUES (" + valcur.ToString() + "," + infop.IDPersoana.ToString() + "," + infop.IDTipPrezenta.ToString() + "," + infop.IDAngajatSalarizare.ToString() + "," + LunaCurenta.ToString() + "," + AnulCurent.ToString() + ")";
                        }
                        else
                        {
                            if (valcur == Decimal.Zero)
                                cmd.CommandText = "DELETE FROM [AngajatiAdaosuri] WHERE [IdPersoana] =" + infop.IDPersoana.ToString() + " AND [IdTipPrezenta]=" + infop.IDTipPrezenta.ToString() + " AND [IdAngajatSalarizare]=" +
                                 infop.IDAngajatSalarizare.ToString();
                            else
                                cmd.CommandText = "UPDATE [AngajatiPontaj] SET [Suma] =" + valcur.ToString() + " WHERE [IdPersoana] =" + infop.IDPersoana.ToString() + " AND [IdTipAdaos]=" + infop.IDTipPrezenta.ToString() + " AND [IdAngajatSalarizare]=" +
                                 infop.IDAngajatSalarizare.ToString(); ;

                        }
                        cate++;
                        cmd.ExecuteNonQuery();
                        infop.ValoareInitiala = valcur;
                    }
                }

            tr.Commit();
            if(cate!=0)
                SetStatus("S-au salvat " + cate.ToString() + " inregistrari la: " + DateTime.Now.ToString());
            InchideConexiune(con);
        }

        private void adaosuriDGV_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
        {
            if (e.ColumnIndex <= adaosuriDGV.Columns["TotalColumn"].Index)
                return;
            Decimal total = Decimal.Zero;
            foreach (DataGridViewCell dc in adaosuriDGV.Rows[e.RowIndex].Cells)
            {
                if (dc.ColumnIndex > adaosuriDGV.Columns["TotalColumn"].Index)
                    if (dc.EditedFormattedValue.ToString()!=String.Empty)
                        total += Convert.ToDecimal(dc.EditedFormattedValue);
            }
            adaosuriDGV["TotalColumn", e.RowIndex].Value = total.ToString();
        }
    }

    public class InfoPrezenta
    {
        public String TimpBaza = String.Empty;
        public int IDPersoana = 0;
        public Decimal ValoareInitiala = Decimal.Zero;
        public int IDTipPrezenta = 0;
        public int IDAngajatSalarizare = 0;
        public Boolean Insert = false;
        public InfoPrezenta(String tb,int idp,Decimal val,int idtp,int idas,Boolean ins)
        {
            TimpBaza = tb;
            IDPersoana = idp;
            ValoareInitiala = val;
            IDTipPrezenta = idtp;
            IDAngajatSalarizare = idas;
            Insert = ins;
        }
    }
}
